SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>Data profiling</title>

</head>

Data profiling

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .ambox{border:1px solid #a2a9b1;border-left:10px solid #36c;background-color:#fbfbfb;box-sizing:border-box}.mw-parser-output .ambox+link+.ambox,.mw-parser-output .ambox+link+style+.ambox,.mw-parser-output .ambox+link+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+style+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+link+.ambox{margin-top:-1px}html body.mediawiki .mw-parser-output .ambox.mbox-small-left{margin:4px 1em 4px 0;overflow:hidden;width:238px;border-collapse:collapse;font-size:88%;line-height:1.25em}.mw-parser-output .ambox-speedy{border-left:10px solid #b32424;background-color:#fee7e6}.mw-parser-output .ambox-delete{border-left:10px solid #b32424}.mw-parser-output .ambox-content{border-left:10px solid #f28500}.mw-parser-output .ambox-style{border-left:10px solid #fc3}.mw-parser-output .ambox-move{border-left:10px solid #9932cc}.mw-parser-output .ambox-protection{border-left:10px solid #a2a9b1}.mw-parser-output .ambox .mbox-text{border:none;padding:0.25em 0.5em;width:100%}.mw-parser-output .ambox .mbox-image{border:none;padding:2px 0 2px 0.5em;text-align:center}.mw-parser-output .ambox .mbox-imageright{border:none;padding:2px 0.5em 2px 0;text-align:center}.mw-parser-output .ambox .mbox-empty-cell{border:none;padding:0;width:1px}.mw-parser-output .ambox .mbox-image-div{width:52px}@media(min-width:720px){.mw-parser-output .ambox{margin:0 10%}}@media print{body.ns-0 .mw-parser-output .ambox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style>

Data profiling is the process of examining the data available from an existing information source (e.g. a database or a <a href="Computer_file" title="Computer file">file</a>) and collecting <a href="Descriptive_statistics" title="Descriptive statistics">statistics</a> or informative summaries about that data.<a href="#cite_note-Johnson2009-1">[1]</a> The purpose of these statistics may be to:

<ol><li>Find out whether existing data can be easily used for other purposes</li>

<li>Improve the ability to search data by <a href="Tag_(metadata)" title="Tag (metadata)">tagging</a> it with <a href="Index_term" title="Index term">keywords</a>, descriptions, or assigning it to a category</li>

<li>Assess <a href="Data_quality" title="Data quality">data quality</a>, including whether the data conforms to particular standards or patterns<a href="#cite_note-2">[2]</a></li>

<li>Assess the risk involved in <a href="Data_integration" title="Data integration">integrating data</a> in new applications, including the challenges of <a href="Join_(SQL)" title="Join (SQL)">joins</a></li>

<li>Discover <a href="Metadata" title="Metadata">metadata</a> of the source database, including value patterns and <a href="Frequency_distribution" class="mw-redirect" title="Frequency distribution">distributions</a>, <a href="Candidate_key" title="Candidate key">key candidates</a>, <a href="Inclusion_dependency" class="mw-redirect" title="Inclusion dependency">foreign-key candidates</a>, and <a href="Functional_dependency" title="Functional dependency">functional dependencies</a></li>

<li>Assess whether known metadata accurately describes the actual values in the source database</li>

<li>Understanding data challenges early in any data intensive project, so that late project surprises are avoided. Finding data problems late in the project can lead to delays and cost overruns.</li>

<li>Have an enterprise view of all data, for uses such as <a href="Master_data_management" title="Master data management">master data management</a>, where key data is needed, or <a href="Data_governance" title="Data governance">data governance</a> for improving data quality.</li></ol>

<div class="mw-heading mw-heading2"><h2 id="Introduction">Introduction</h2></div>

Data profiling refers to the analysis of information for use in a <a href="Data_warehouse" title="Data warehouse">data warehouse</a> in order to clarify the structure, content, relationships, and derivation rules of the data.<a href="#cite_note-Kimball2008-3">[3]</a> Profiling helps to not only understand anomalies and assess data quality, but also to discover, register, and assess enterprise metadata.<a href="#cite_note-Loshin2009-4">[4]</a><a href="#cite_note-Loshin2003-5">[5]</a> The result of the analysis is used to determine the suitability of the candidate source systems, usually giving the basis for an early go/no-go decision, and also to identify problems for later solution design.<a href="#cite_note-Kimball2008-3">[3]</a>

<div class="mw-heading mw-heading2"><h2 id="How_data_profiling_is_conducted">How data profiling is conducted</h2></div>

Data profiling utilizes methods of descriptive statistics such as minimum, maximum, mean, mode, percentile, standard deviation, frequency, variation, aggregates such as count and sum, and additional metadata information obtained during data profiling such as data type, length, discrete values, uniqueness, occurrence of null values, typical string patterns, and abstract type recognition.<a href="#cite_note-Loshin2009-4">[4]</a><a href="#cite_note-Rahm2000-6">[6]</a><a href="#cite_note-Singh2010-7">[7]</a> The metadata can then be used to discover problems such as illegal values, misspellings, missing values, varying value representation, and duplicates.

Different analyses are performed for different structural levels. E.g. single columns could be profiled individually to get an understanding of frequency distribution of different values, type, and use of each column. Embedded value dependencies can be exposed in a cross-columns analysis. Finally, overlapping value sets possibly representing foreign key relationships between entities can be explored in an inter-table analysis.<a href="#cite_note-Loshin2009-4">[4]</a>

Normally, purpose-built tools are used for data profiling to ease the process.<a href="#cite_note-Kimball2008-3">[3]</a><a href="#cite_note-Loshin2009-4">[4]</a><a href="#cite_note-Rahm2000-6">[6]</a><a href="#cite_note-Singh2010-7">[7]</a><a href="#cite_note-Kimball2004-8">[8]</a><a href="#cite_note-Olson2003-9">[9]</a> The computational complexity increases when going from single column, to single table, to cross-table structural profiling. Therefore, performance is an evaluation criterion for profiling tools.<a href="#cite_note-Loshin2003-5">[5]</a>

<div class="mw-heading mw-heading2"><h2 id="When_is_data_profiling_conducted?">When is data profiling conducted?</h2></div>

According to Kimball,<a href="#cite_note-Kimball2008-3">[3]</a> data profiling is performed several times and with varying intensity throughout the data warehouse developing process. A light profiling assessment should be undertaken immediately after candidate source systems have been identified and DW/BI business requirements have been satisfied. The purpose of this initial analysis is to clarify at an early stage if the correct data is available at the appropriate detail level and that anomalies can be handled subsequently. If this is not the case the project may be terminated.<a href="#cite_note-Kimball2008-3">[3]</a>

Additionally, more in-depth profiling is done prior to the dimensional modeling process in order assess what is required to convert data into a dimensional model. Detailed profiling extends into the ETL system design process in order to determine the appropriate data to extract and which filters to apply to the data set.<a href="#cite_note-Kimball2008-3">[3]</a>

Additionally, data profiling may be conducted in the data warehouse development process after data has been loaded into staging, the data marts, etc. Conducting data at these stages helps ensure that data cleaning and transformations have been done correctly and in compliance of requirements.

<div class="mw-heading mw-heading2"><h2 id="Benefits_and_examples">Benefits and examples</h2></div>

The benefits of data profiling are to improve data quality, shorten the implementation cycle of major projects, and improve users' understanding of data.<a href="#cite_note-Olson2003-9">[9]</a> Discovering business knowledge embedded in data itself is one of the significant benefits derived from data profiling.<a href="#cite_note-Loshin2003-5">[5]</a> Data profiling is one of the most effective technologies for improving data accuracy in corporate databases.<a href="#cite_note-Olson2003-9">[9]</a>

<ul><li><a href="Data_quality" title="Data quality">Data quality</a></li>

<li><a href="Data_governance" title="Data governance">Data governance</a></li>

<li><a href="Master_data_management" title="Master data management">Master data management</a></li>

<li><a href="Database_normalization" title="Database normalization">Database normalization</a></li>

<li><a href="Data_visualization" class="mw-redirect" title="Data visualization">Data visualization</a></li>

<li><a href="Analysis_paralysis" title="Analysis paralysis">Analysis paralysis</a></li>

<li><a href="Data_analysis" title="Data analysis">Data analysis</a></li></ul>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("./mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("./mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("./mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("./mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite id="CITEREFJohnson2009" class="citation encyclopaedia cs1">Johnson, Theodore (2009). "Data Profiling". In Springer, Heidelberg (ed.). Encyclopedia of Database Systems.</cite>

</li>

<li id="cite_note-2"><a href="#cite_ref-2">^</a> <cite id="CITEREFWoodallOberhoferBorek2014" class="citation journal cs1">Woodall, Philip; Oberhofer, Martin; Borek, Alexander (2014). <a rel="nofollow" class="external text" href="http://www.inderscience.com/link.php?id=68656">"A classification of data quality assessment and improvement methods"</a>. International Journal of Information Quality. 3 (4): 298. <a href="Doi_(identifier)" class="mw-redirect" title="Doi (identifier)">doi</a>:<a rel="nofollow" class="external text" href="https://doi.org/10.1504%2Fijiq.2014.068656">10.1504/ijiq.2014.068656</a>.</cite>

</li>

<li id="cite_note-Kimball2008-3">^ <a href="#cite_ref-Kimball2008_3-0">a</a> <a href="#cite_ref-Kimball2008_3-1">b</a> <a href="#cite_ref-Kimball2008_3-2">c</a> <a href="#cite_ref-Kimball2008_3-3">d</a> <a href="#cite_ref-Kimball2008_3-4">e</a> <a href="#cite_ref-Kimball2008_3-5">f</a> <cite id="CITEREFKimball2008" class="citation book cs1">Kimball, Ralph; et al. (2008). <a rel="nofollow" class="external text" href="https://archive.org/details/datawarehouselif00kimb_924">The Data Warehouse Lifecycle Toolkit</a> (Second ed.). Wiley. pp. <a rel="nofollow" class="external text" href="https://archive.org/details/datawarehouselif00kimb_924/page/n17">376</a>. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>9780470149775</bdi>.</cite>

</li>

<li id="cite_note-Loshin2009-4">^ <a href="#cite_ref-Loshin2009_4-0">a</a> <a href="#cite_ref-Loshin2009_4-1">b</a> <a href="#cite_ref-Loshin2009_4-2">c</a> <a href="#cite_ref-Loshin2009_4-3">d</a> <cite id="CITEREFLoshin2009" class="citation book cs1">Loshin, David (2009). <a rel="nofollow" class="external text" href="https://archive.org/details/masterdatamanage00losh">Master Data Management</a>. Morgan Kaufmann. pp. <a rel="nofollow" class="external text" href="https://archive.org/details/masterdatamanage00losh/page/n197">94</a>–96. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>9780123742254</bdi>.</cite>

</li>

<li id="cite_note-Loshin2003-5">^ <a href="#cite_ref-Loshin2003_5-0">a</a> <a href="#cite_ref-Loshin2003_5-1">b</a> <a href="#cite_ref-Loshin2003_5-2">c</a> <cite id="CITEREFLoshin2003" class="citation book cs1">Loshin, David (2003). Business Intelligence: The Savvy Manager's Guide, Getting Onboard with Emerging IT. Morgan Kaufmann. pp. 110–111. <a href="ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>9781558609167</bdi>.</cite>

</li>

<li id="cite_note-Rahm2000-6">^ <a href="#cite_ref-Rahm2000_6-0">a</a> <a href="#cite_ref-Rahm2000_6-1">b</a> <cite id="CITEREFRahmHai_Do2000" class="citation journal cs1">Rahm, Erhard; Hai Do, Hong (December 2000). "Data Cleaning: Problems and Current Approaches". Bulletin of the Technical Committee on Data Engineering. 23 (4). IEEE Computer Society.</cite>

</li>

<li id="cite_note-Singh2010-7">^ <a href="#cite_ref-Singh2010_7-0">a</a> <a href="#cite_ref-Singh2010_7-1">b</a> <cite id="CITEREFSinghSingh2010" class="citation journal cs1">Singh, Ranjit; Singh, Kawaljeet; et al. (May 2010). "A Descriptive Classification of Causes of Data Quality Problems in Data Warehousing". IJCSI International Journal of Computer Science Issue. 2. 7 (3).</cite>

</li>

<li id="cite_note-Kimball2004-8"><a href="#cite_ref-Kimball2004_8-0">^</a> <cite id="CITEREFKimball2004" class="citation web cs1">Kimball, Ralph (2004). <a rel="nofollow" class="external text" href="http://www.kimballgroup.com/wp-content/uploads/2012/05/DT59SurprisingValue.pdf">"Kimball Design Tip #59: Surprising Value of Data Profiling"</a> (PDF). Kimball Group.</cite>

</li>

<li id="cite_note-Olson2003-9">^ <a href="#cite_ref-Olson2003_9-0">a</a> <a href="#cite_ref-Olson2003_9-1">b</a> <a href="#cite_ref-Olson2003_9-2">c</a> <cite id="CITEREFOlson2003" class="citation book cs1">Olson, Jack E. (2003). <a rel="nofollow" class="external text" href="https://archive.org/details/dataqualityaccur00olso_641">Data Quality: The Accuracy Dimension</a>. Morgan Kaufmann. pp. <a rel="nofollow" class="external text" href="https://archive.org/details/dataqualityaccur00olso_641/page/n159">140</a>–142.</cite>

</li>

</ol></div></div></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2025-06-23" href="https://en.wikipedia.org/wiki/?title=Data_profiling&oldid=1297044642">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>